﻿/********************************************************************************************************
  1. 스토어드명		: SP_CODE_MANAGEMENT_CUD
  2. 목적 & 기능		: Code에 대한 입력,수정, 삭제관리 (Cad Code / Part Code / Purchasing Part Code)
  3. 생성일자		: 2014.06.30
  4. 생성자 			: 조범준
  5. Used DB Name	: DB_EDUC
  6. Used DB Table	:
						Create	- STDCODE a
						Update	- STDCODE a
						Delete	- STDCODE a
  7. 실행			:
						SP_CODE_MANAGEMENT_CUD 'CREATE_CODE', '', '', '', '', ''
						SP_CODE_MANAGEMENT_CUD 'UPDATE_CODE', '', '', '', '', ''
						SP_CODE_MANAGEMENT_CUD 'DELETE_CODE', '', '', '', '', ''
 ********************************************************************************************************/
----------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SP_CODE_MANAGEMENT_CUD
(
	iJOBMODE				IN VARCHAR2,
	iOBJECTID				IN NUMBER,
	iPAR_OBJECTID			IN NUMBER,
	iGRP_OBJECTID			IN NUMBER,
	iCODE					IN VARCHAR2,
	iPATTERN				IN VARCHAR2,
	iKOR_NAME				IN VARCHAR2
)
IS
BEGIN
----------------------------------------------------------------------------------------------------------
-- 작업구분
----------------------------------------------------------------------------------------------------------
    IF iJOBMODE = 'CREATE_CODE' THEN
        GOTO CREATE_CODE;
    ELSIF iJOBMODE = 'UPDATE_CODE' THEN
        GOTO UPDATE_CODE;
    ELSIF iJOBMODE = 'DELETE_CODE' THEN
        GOTO DELETE_CODE;
    END IF;

----------------------------------------------------------------------------------------------------------
-- CODE 추가
----------------------------------------------------------------------------------------------------------
<<CREATE_CODE>>
	INSERT INTO STDCODE (OBJECTID, PAR_OBJECTID, GRP_OBJECTID, CODE, PATTERN, KOR_NAME, CREATE_DATE)
	VALUES (SEQ_STDCODE.NEXTVAL, iPAR_OBJECTID, iGRP_OBJECTID, iCODE, iPATTERN, iKOR_NAME, SYSDATE);
	RETURN;
----------------------------------------------------------------------------------------------------------
-- CODE 수정
----------------------------------------------------------------------------------------------------------
<<UPDATE_CODE>>
DECLARE	nRootObjectId	NUMBER;
	
	BEGIN
		nRootObjectId := -999;

		----------------------------------------------------------------------------------------------------------
		--Root Node의 ObjectId 값 읽기
		----------------------------------------------------------------------------------------------------------
		SELECT	MAX(connect_by_root a.OBJECTID) INTO nRootObjectId
		FROM	STDCODE a
		WHERE	GRP_OBJECTID = iGRP_OBJECTID AND
				PAR_OBJECTID = iPAR_OBJECTID
		START WITH	a.PAR_OBJECTID = -1
		CONNECT BY PRIOR	a.OBJECTID = a.PAR_OBJECTID;

		IF nRootObjectId != -999 THEN
		BEGIN
		----------------------------------------------------------------------------------------------------------
		--변경하려는 값 수정
		----------------------------------------------------------------------------------------------------------
			UPDATE	STDCODE a
			SET		a.PAR_OBJECTID	= iPAR_OBJECTID,
					a.CODE			= iCODE,
					a.PATTERN		= iPATTERN,
					a.KOR_NAME		= iKOR_NAME,
					a.MODIFY_DATE	= SYSDATE
			WHERE	a.OBJECTID		= iOBJECTID;

		----------------------------------------------------------------------------------------------------------
		--변경하려는 값 Child 수정
		----------------------------------------------------------------------------------------------------------
			UPDATE	STDCODE d
			SET		d.PATTERN =
					(
						SELECT REPLACE(c.PATTERN, '▽', '') AS PATTERN
						FROM
						(
							SELECT	b.OBJECTID, sys_connect_by_path(b.CODE,'▽') AS PATTERN
							FROM	STDCODE b
							WHERE	b.GRP_OBJECTID = iGRP_OBJECTID	AND
									b.OBJECTID IN	(
														SELECT	a.OBJECTID
														FROM	STDCODE a
														WHERE	a.GRP_OBJECTID = iGRP_OBJECTID
														START WITH	a.OBJECTID = iOBJECTID
														CONNECT BY PRIOR	a.OBJECTID = a.PAR_OBJECTID
													)
							START WITH b.OBJECTID IN (
														SELECT  a.OBJECTID
														FROM	STDCODE a
														WHERE	a.GRP_OBJECTID = iGRP_OBJECTID
														START WITH	a.OBJECTID = nRootObjectId
														CONNECT BY	a.OBJECTID = PRIOR a.PAR_OBJECTID
												   )
							CONNECT BY b.PAR_OBJECTID = PRIOR b.OBJECTID
						) c
						WHERE	d.OBJECTID = c.OBJECTID
					)
			WHERE	d.OBJECTID IN (
									SELECT	a.OBJECTID
									FROM	STDCODE a
									WHERE	a.GRP_OBJECTID = iGRP_OBJECTID
									START WITH	a.OBJECTID = iOBJECTID
									CONNECT BY PRIOR	a.OBJECTID = a.PAR_OBJECTID
									);
		END;
		END IF;
	END;
	
	RETURN;
----------------------------------------------------------------------------------------------------------
-- CODE 삭제
----------------------------------------------------------------------------------------------------------
<<DELETE_CODE>>
	DELETE
	FROM	STDCODE a
	WHERE	a.OBJECTID	IN (
							SELECT	b.OBJECTID
							FROM	STDCODE b START
							WITH	b.PAR_OBJECTID = iOBJECTID
							CONNECT BY PRIOR	B.OBJECTID = B.PAR_OBJECTID
							);
	DELETE
	FROM	STDCODE a
	WHERE	a.OBJECTID = iOBJECTID;    
	RETURN;
----------------------------------------------------------------------------------------------------------
END SP_CODE_MANAGEMENT_CUD;
